from datetime import datetime, timedelta
from sqlalchemy import func, or_
from models import db, Product, Location, Inventory, Batch

class WarehouseService:
    
    @staticmethod
    def get_low_stock_products(threshold=None):
        """库存预警 - 获取低于库存阈值的商品"""
        query = db.session.query(
            Product.id,
            Product.name,
            Product.barcode,
            Product.min_stock,
            func.coalesce(func.sum(Inventory.quantity), 0).label('current_stock')
        ).join(Inventory, Inventory.product_id == Product.id, isouter=True)
        
        if threshold is not None:
            query = query.group_by(Product.id).having(
                func.coalesce(func.sum(Inventory.quantity), 0) < threshold
            )
        else:
            query = query.group_by(Product.id).having(
                func.coalesce(func.sum(Inventory.quantity), 0) < Product.min_stock
            )
        
        return query.all()
    
    @staticmethod
    def get_expiring_batches(days=30):
        """批次管理 - 获取即将过期的批次"""
        threshold_date = datetime.now().date() + timedelta(days=days)
        return Batch.query.filter(
            Batch.expiry_date <= threshold_date,
            Batch.expiry_date >= datetime.now().date(),
            Batch.quantity > 0
        ).order_by(Batch.expiry_date).all()
    
    @staticmethod
    def search_products(keyword=None, barcode=None, category=None):
        """商品查询 - 多条件搜索商品"""
        query = Product.query
        
        if keyword:
            query = query.filter(or_(
                Product.name.contains(keyword),
                Product.description.contains(keyword)
            ))
        
        if barcode:
            query = query.filter(Product.barcode == barcode)
            
        if category:
            query = query.filter(Product.category == category)
            
        return query.all()
    
    @staticmethod
    def get_product_inventory(product_id):
        """商品查询 - 获取商品库存详情"""
        return db.session.query(
            Inventory.location_id,
            Location.code.label('location_code'),
            Inventory.quantity
        ).join(Location, Location.id == Inventory.location_id
        ).filter(Inventory.product_id == product_id).all()
    
    @staticmethod
    def get_location_usage():
        """货位管理 - 获取货位使用情况"""
        return db.session.query(
            Location.id,
            Location.code,
            Location.area,
            func.count(Inventory.id).label('product_count'),
            func.sum(Inventory.quantity).label('total_quantity')
        ).join(Inventory, Inventory.location_id == Location.id, isouter=True
        ).group_by(Location.id).all()
    
    @staticmethod
    def add_batch(product_id, batch_number, quantity, production_date, 
                 expiry_date, supplier, purchase_price, location_id=None):
        """批次管理 - 添加新批次"""
        # 创建批次记录
        batch = Batch(
            product_id=product_id,
            batch_number=batch_number,
            quantity=quantity,
            production_date=production_date,
            expiry_date=expiry_date,
            supplier=supplier,
            purchase_price=purchase_price,
            location_id=location_id
        )
        db.session.add(batch)
        
        # 更新库存
        if location_id:
            inventory = Inventory.query.filter_by(
                product_id=product_id,
                location_id=location_id
            ).first()
            
            if inventory:
                inventory.quantity += quantity
                inventory.last_updated = datetime.now()
            else:
                inventory = Inventory(
                    product_id=product_id,
                    location_id=location_id,
                    quantity=quantity
                )
                db.session.add(inventory)
        
        db.session.commit()
        return batch
    
    @staticmethod
    def move_inventory(product_id, from_location_id, to_location_id, quantity):
        """货位管理 - 移动库存"""
        # 检查源货位是否有足够库存
        from_inventory = Inventory.query.filter_by(
            product_id=product_id,
            location_id=from_location_id
        ).first()
        
        if not from_inventory or from_inventory.quantity < quantity:
            raise ValueError("源货位库存不足")
        
        # 减少源货位库存
        from_inventory.quantity -= quantity
        
        # 增加目标货位库存
        to_inventory = Inventory.query.filter_by(
            product_id=product_id,
            location_id=to_location_id
        ).first()
        
        if to_inventory:
            to_inventory.quantity += quantity
            to_inventory.last_updated = datetime.now()
        else:
            to_inventory = Inventory(
                product_id=product_id,
                location_id=to_location_id,
                quantity=quantity
            )
            db.session.add(to_inventory)
        
        db.session.commit()
        return True
